package com.briup.house;

import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HouseServer {
    Connection connection;

    public Connection getConnection() throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
         connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3305/house",
                "root",
                "root"
        );

        return connection;
    }

    //添加house数据
    public boolean add(House house) throws SQLException, ClassNotFoundException {
        //编写sql语句
        String sql="insert into house values(?,?,?,?,?,?)";

        //获取Statement对象
        PreparedStatement statement = this.getConnection().prepareStatement(sql);
        statement.setInt(1,house.getId());
        statement.setString(2,house.getName());
        statement.setString(3,house.getPhone());
        statement.setString(4,house.getAddress());
        statement.setInt(5,house.getRent());
        statement.setString(6,house.getState());
        //执行sql
        int num = statement.executeUpdate();
        //判断是否插入成功
        if (num>0){
            System.out.println("新增房屋成功!");
        }

        return true;
    }

    public boolean delete(int id) throws SQLException, ClassNotFoundException {
        String sql="delete from house where id=?";
        PreparedStatement statement = this.getConnection().prepareStatement(sql);
        statement.setInt(1,id);
        int num = statement.executeUpdate();
        if (num>0){
            System.out.println("删除成功!");
        }
        return true;
    }
    public boolean selectByID(int id) throws SQLException, ClassNotFoundException {
        String sql="select * from house where id=?";
        PreparedStatement statement = this.getConnection().prepareStatement(sql);
        statement.setInt(1,id);
        ResultSet resultSet = statement.executeQuery();
        House  house= new House();
        while (resultSet.next()){
            house.setId(resultSet.getInt("id"));
            house.setName(resultSet.getString("name"));
            house.setPhone(resultSet.getString("phone"));
            house.setAddress(resultSet.getString("address"));
            house.setRent(resultSet.getInt("rent"));
            house.setState(resultSet.getString("state"));
        }
        System.out.println(house);

     return  true;

    }
    public boolean update(House house) throws SQLException, ClassNotFoundException {
        //获取id name phone address rent status
        int id=house.getId();
        String name=house.getName();
        String phone=house.getPhone();
        String address=house.getAddress();
        int rent=house.getRent();
        String state=house.getState();

        String sql="update house set name=?,phone=?,address=?,rent=?,state=? where id=?";
        PreparedStatement statement = this.getConnection().prepareStatement(sql);
        if (name!=null){
            statement.setString(1,house.getName());
        }
        if (phone!=null){
            statement.setString(2,house.getPhone());
        }
        if (address!=null){
            statement.setString(3,house.getAddress());
        }
        if (rent!=0){
            statement.setInt(4,house.getRent());
        }
        if (state!=null){
            statement.setString(5,house.getState());
        }
        if (id!=0){
            statement.setInt(6,house.getId());
        }
        int num = statement.executeUpdate();
        if (num>0){
            System.out.println("修改成功");
        }
        return true;

    }

    //列表
    public List<House> list() throws SQLException, ClassNotFoundException {
        String sql="select * from house";
        PreparedStatement statement = this.getConnection().prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        //处理结果集
        List<House> list=new ArrayList();

        while (resultSet.next()){
            House house = new House();
            house.setId(resultSet.getInt("id"));
            //name
            house.setName(resultSet.getString("name"));
            //phone
            house.setPhone(resultSet.getString("phone"));
            //address
            house.setAddress(resultSet.getString("address"));
            //rent
            house.setRent(resultSet.getInt("rent"));
            //status
            house.setState(resultSet.getString("state"));
            list.add(house);
        }
        System.out.println(list);
        return list;
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        House house = new House(5, "zs", "133444555", "hahsh", 1222, "出租");
        HouseServer houseServer = new HouseServer();
        houseServer.add(house);

    }





}
